package com.zsqx.tools;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.security.spec.ECField;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;

import cDao.HelpDao;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelTools {

	public static String writeToExcel(String title, String absolute_path,
			List list, Map<String, String> map) throws NullPointerException,
			RowsExceededException, WriteException, IOException {
		String imageUrl = PropertiesTools.getImageUploadURL(absolute_path);
		String fileName=PropertiesTools.getFileName()+".xls";
		String excel_path=absolute_path+PropertiesTools.getExcelUploadURL()+fileName;
		WritableWorkbook wwb = Workbook.createWorkbook(new File(excel_path));
		WritableSheet ws = wwb.createSheet(title, 0);
		Class c = list.get(0).getClass();
		Field[] fileds = c.getDeclaredFields();
		int i = 0, j = 0;
		for (Field field : fileds) {
			if (map.containsKey(field.getName())) {
				String filedName = map.get(field.getName());
				Label label_reTime = new Label(i, 0, filedName);
				ws.addCell(label_reTime);
				i++;
			}
		}
		for (; j < list.size(); j++) {
			Object bean = list.get(j);
			int z = 0;
			for (Field field : fileds) {
				if (map.containsKey(field.getName())) {
					String methodName = HelpDao.getGetMethod(field.getName());
					Object value = HelpDao.methodInvoke(c, bean, methodName);
					String cellValue = "";
					// System.out.println(field.getName()+field.getType());
					if (field.getType() == Date.class) {
						// System.out.println("Date");
						if (null != value) {
							cellValue = new SimpleDateFormat(
									"yyyy-MM-dd HH:mm:ss").format(value);
						}
						Label label = new Label(z, j + 1, cellValue);
						ws.addCell(label);
					} else if (field.getType() == String.class
							&& map.get(field.getName()).indexOf("图片") != -1) {
						// System.out.println("pic");
						File outputFile = null;
						// System.out.println(imageUrl + value);
						File imgFile = new File(imageUrl + value);
						if (imgFile.exists() && imgFile.length() > 0) {
							BufferedImage input = null;
							try {
								input = ImageIO.read(imgFile);
							} catch (Exception e) {
								e.printStackTrace();
							}
							if (input != null) {
								String path = imgFile.getAbsolutePath();
								outputFile = new File(path.substring(0,
										path.lastIndexOf('.') + 1)
										+ "png");
								ImageIO.write(input, "PNG", outputFile);
								if (outputFile.exists()
										&& outputFile.length() > 0) {
									WritableImage image = new WritableImage(z,
											j + 1, 1, 1, outputFile);
									ws.addImage(image);
								}
							}
						} else {
							Label label_reImage = new Label(z, j + 1, "无上传图片");
							ws.addCell(label_reImage);
						}
					} else {
						// System.out.println("other");
						if (field.getType() != File.class && null != value) {
							cellValue = value.toString();
						}
						Label label = new Label(z, j + 1, cellValue);
						ws.addCell(label);
					}
					z++;
				}
			}
		}
		wwb.write();
		wwb.close();
		return fileName;
	}

}
